﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using HydroDesktop.Database.Map;
using HydroDesktop.ObjectModel;
using HydroDesktop.Database;
using HydroDesktop.Database.DataManagers;
using System.Data;
using System.Data.Common;
using NHibernate;

namespace HydroDesktop.Database
{
	/// <summary>
	/// This class is responsible for communication with the 'Metadata Cache' database
    /// This is an alternative implementation internally using SQL queries instead of NHibernate.
    /// This is to compare saving speed.
	/// </summary>
	public class MetadataCacheManagerNh
	{
		#region Variables

        //helper class which communicates with the database
       private static NHibernateHelper _hibernate;

        private static ISessionFactory _factory;

        public static readonly RepositoryManager Instance = new RepositoryManager();
        
        #endregion

        #region Constructor
         public MetadataCacheManagerNh()
         {
             if (_factory == null)
             {
                _factory= FluentlyConfigure( DatabaseTypes.SQLite,Config.DefaultLocalCacheConnection());

                _hibernate = new NHibernateHelper(_factory);
             }
            
         }
        /// <summary>
        /// Creates a new instance of the manager given a connection string
        /// </summary>
        /// <param name="dbType">The type of the database (SQLite, SQLServer, ...)</param>
        /// <param name="connectionString">The connection string</param>
        public MetadataCacheManagerNh(DatabaseTypes dbType, string connectionString)
        {
            _factory = FluentlyConfigure(dbType, connectionString);
            _hibernate = new NHibernateHelper(_factory);
           
        }

        private ISessionFactory FluentlyConfigure(DatabaseTypes dbType, string connectionString)
        {
            var cfg = Fluently.Configure();
            
            switch(dbType)
            {
                case DatabaseTypes.SQLite:
                    cfg.Database
                        //    (SQLiteConfiguration.Standard.ConnectionString(connectionString).ShowSql()); // showSql does not appear to effect performance
                        //  (SQLiteConfiguration.Standard.ConnectionString(connectionString)); // showSql does not appear to effect performance
                        (SQLiteConfiguration.Standard.ConnectionString(connectionString)
                             .Cache(c => c.UseQueryCache().ProviderClass<NHibernate.Caches.SysCache2.SysCacheProvider>()).ShowSql());
                    break;
                case DatabaseTypes.SQLServer:
                    cfg.Database
                        (MsSqlConfiguration.MsSql2005.ConnectionString(connectionString).ShowSql());
                    break;
            }
           
        

            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.DataServiceMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.ISOMetadataMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.MethodMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.QualityControlLevelMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.SeriesMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.SiteMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.SourceMap>());
            cfg.Mappings(m => m.FluentMappings.Add<HydroDesktop.Database.Map.MetadataCache.VariableMap>());
            
            ISessionFactory factory = cfg.BuildSessionFactory();
           
           return factory;


        }
        #endregion

        private bool NumberIsBetween ( double numberToCheck, double bounds1, double bounds2, bool inclusiveAtBounds )
		{
			double lowerBound, upperBound;

			if ( bounds1 > bounds2 )
			{
				lowerBound = bounds2;
				upperBound = bounds1;
			}
			else
			{
				lowerBound = bounds1;
				upperBound = bounds2;
			}

			if ( inclusiveAtBounds == true )
			{
				return (numberToCheck >= lowerBound && numberToCheck <= upperBound);
			}
			else
			{
				return (numberToCheck > lowerBound && numberToCheck < upperBound);
			}
		}

		private bool EnvelopesIntersect ( double env1xMin, double env1xMax, double env1yMin, double env1yMax, double env2xMin, double env2xMax, double env2yMin, double env2yMax )
		{
			return (((NumberIsBetween ( env1xMin, env2xMin, env2xMax, true ) || NumberIsBetween ( env1xMax, env2xMin, env2xMax, true )) && (NumberIsBetween ( env1yMin, env2yMin, env2yMax, true ) || NumberIsBetween ( env1yMax, env2yMin, env2yMax, true ))) ||
					((NumberIsBetween ( env2xMin, env1xMin, env1xMax, true ) || NumberIsBetween ( env2xMax, env1xMin, env1xMax, true )) && (NumberIsBetween ( env2yMin, env1yMin, env1yMax, true ) || NumberIsBetween ( env2yMax, env1yMin, env1yMax, true ))));
		}

		private bool PointIntersectsEnvelope ( double pointX, double pointY, double envXMin, double envXMax, double envYMin, double envYMax )
		{
			return (NumberIsBetween ( pointX, envXMin, envXMax, true ) && NumberIsBetween ( pointY, envYMin, envYMax, true ));
		}

		private bool DateRangesOverlap ( DateTime startDate1, DateTime endDate1, DateTime startDate2, DateTime endDate2 )
		{
			return (((startDate1 >= startDate2) && (startDate1 <= endDate2)) ||
					((endDate1 >= startDate2) && (endDate1 <= endDate2)) ||
					((startDate2 >= startDate1) && (startDate2 <= endDate1)) ||
					((endDate2 >= startDate1) && (endDate2 <= endDate1)));
		}

        //public MethodDAO MethodDAO { get; protected set; }

        //public QualityControlLevelDAO QualityControlLevelDAO { get; protected set; }

        //public SiteDAO SiteDAO { get; protected set; }

        //public SourceDAO SourceDAO { get; protected set; }

        //public ISOMetadataDAO ISOMetadataDAO { get; protected set; }

        //public VariableDAO VariableDAO { get; protected set; }

        //public DataServiceDAO DataServiceDAO { get; protected set; }

		#region Public Methods

		/// <summary>
		/// Get all data services saved in the metadata cache database
		/// </summary>
		public IList<DataServiceInfo> GetAllServices ()
		{
            ISession session = _hibernate.GetCurrentSession();
            ICriteria criteria = session.CreateCriteria<DataServiceInfo>();
            return criteria.List<DataServiceInfo>();
            
		}

		#endregion

		public void GetSitesInBox ( double xMin, double xMax, double yMin, double yMax )
		{
			throw new System.NotImplementedException ();
		}

		public IList<DataServiceInfo> GetServicesInBox ( double xMin, double xMax, double yMin, double yMax )
		{
            throw new NotImplementedException();
			//IList<DataServiceInfo> services = null;

            //string sql = "SELECT * FROM DataServicesCache WHERE " +
            //    String.Format("EastLongitude BETWEEN {0} AND {1}", xMin, xMax) +
            //    String.Format("OR WestLongitude BETWEEN {0} AND {1}", xMin, xMax) +
            //    String.Format("OR NorthLatitude BETWEEN {0} AND {1}", yMin, yMax) +
            //    String.Format("OR SouthLatitude BETWEEN {0} AND {1}", yMin, yMax);

            //DataTable tbl = _db.LoadTable(sql);

            //IList<DataServiceInfo> services = null;

            //if (tbl.Rows.Count > 0)
            //{
            //    services = new List<DataServiceInfo>();
            //    foreach (System.Data.DataRow row in tbl.Rows)
            //    {
            //        services.Add(ServiceFromDataRow(row));
            //    }
            //}
            //return services;
		}

        private string DetailedSeriesSQLQuery()
        {
            string sql = "SELECT SeriesID, " +
                "SiteName, SiteCode, Latitude, Longitude, " +
                "VariableName, VariableCode, DataType, ValueType, Speciation, SampleMedium, " +
                "TimeSupport, GeneralCategory, NoDataValue, " +
                "VariableUnitsName, TimeUnitsName, " +
                "Organization, SourcesCache.Citation, " +
                "BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ValueCount, ServiceCode, ServiceEndpointURL " +
                "FROM DataSeriesCache " +
                "INNER JOIN SitesCache ON DataSeriesCache.SiteID = SitesCache.SiteID " +
                "INNER JOIN VariablesCache ON DataSeriesCache.VariableID = VariablesCache.VariableID " + 
                "INNER JOIN SourcesCache ON DataSeriesCache.SourceID = SourcesCache.SourceID " +
                "INNER JOIN DataServices ON DataSeriesCache.ServiceID = DataServices.ServiceID";
            return sql;
        }

        private DataServiceInfo ServiceFromDataRow(System.Data.DataRow row)
        {
            DataServiceInfo dsi = new DataServiceInfo();
            dsi.Id = DataReader.ReadInteger(row["ServiceID"]);
            dsi.ServiceCode = DataReader.ReadString(row["ServiceCode"]);
            dsi.ServiceName = DataReader.ReadString(row["ServiceName"]);
            dsi.ServiceType = DataReader.ReadString(row["ServiceType"]);
            dsi.Version = DataReader.ReadDouble(row["ServiceVersion"]);
            dsi.Protocol = DataReader.ReadString(row["ServiceProtocol"]);
            dsi.EndpointURL = DataReader.ReadString(row["ServiceEndpointURL"]);
            dsi.DescriptionURL = DataReader.ReadString(row["ServiceDescriptionURL"]);
            dsi.NorthLatitude = DataReader.ReadDouble(row["NorthLatitude"]);
            dsi.SouthLatitude = DataReader.ReadDouble(row["SouthLatitude"]);
            dsi.EastLongitude = DataReader.ReadDouble(row["EastLongitude"]);
            dsi.WestLongitude = DataReader.ReadDouble(row["WestLongitude"]);
            dsi.Abstract = DataReader.ReadString(row["Abstract"]);
            dsi.ContactEmail = DataReader.ReadString(row["ContactEmail"]);
            dsi.ContactName = DataReader.ReadString(row["ContactName"]);
            dsi.Citation = DataReader.ReadString(row["Citation"]);
            dsi.IsHarvested = DataReader.ReadBoolean(row["IsHarvested"]);
            dsi.HarveDateTime = DataReader.ReadDateTime(row["HarveDateTime"]);
            dsi.ServiceTitle = DataReader.ReadString(row["ServiceTitle"]);
            return dsi;
        }

        private SeriesMetadata SeriesFromDataRow(DataRow row)
        {
            Site site = new Site();
            site.Name = Convert.ToString(row["SiteName"]);
            site.Code = Convert.ToString(row["SiteCode"]);
            site.Latitude = Convert.ToDouble(row["Latitude"]);
            site.Longitude = Convert.ToDouble(row["Longitude"]);

            Variable v = new Variable();
            v.Name = Convert.ToString(row["VariableName"]);
            v.Code = Convert.ToString(row["VariableCode"]);
            v.DataType = Convert.ToString(row["DataType"]);
            v.ValueType = Convert.ToString(row["ValueType"]);
            v.Speciation = Convert.ToString(row["Speciation"]);
            v.SampleMedium = Convert.ToString(row["SampleMedium"]);
            v.TimeSupport = Convert.ToDouble(row["TimeSupport"]);
            v.GeneralCategory = Convert.ToString(row["GeneralCategory"]);
            v.VariableUnit = Unit.Unknown;
            v.VariableUnit.Name = Convert.ToString(row["VariableUnitsName"]);
            v.TimeUnit = Unit.UnknownTimeUnit;
            v.TimeUnit.Name = Convert.ToString(row["TimeUnitsName"]);

            Method m = Method.Unknown;
            //m.Description = Convert.ToString(row["MethodDescription"]);

            Source src = Source.Unknown;
            //src.Description = Convert.ToString(row["SourceDescription"]);
            src.Organization = Convert.ToString(row["Organization"]);
            src.Citation = Convert.ToString(row["Citation"]);

            QualityControlLevel qc = QualityControlLevel.Unknown;
            //qc.Code = Convert.ToString(row["QualityControlLevelCode"]);
            //qc.Definition = Convert.ToString(row["QualityControlLevelDefinition"]);

            SeriesMetadata newSeries = new SeriesMetadata(site, v, m, qc, src);
            newSeries.BeginDateTime = Convert.ToDateTime(row["BeginDateTime"]);
            newSeries.EndDateTime = Convert.ToDateTime(row["EndDateTime"]);
            newSeries.BeginDateTimeUTC = Convert.ToDateTime(row["BeginDateTimeUTC"]);
            newSeries.EndDateTimeUTC = Convert.ToDateTime(row["EndDateTimeUTC"]);
            newSeries.ValueCount = Convert.ToInt32(row["ValueCount"]);

            DataServiceInfo servInfo = new DataServiceInfo();
            servInfo.EndpointURL = Convert.ToString(row["ServiceEndpointURL"]);
            //servInfo.ServiceCode = Convert.ToString(row["ServiceCode"]);
            newSeries.DataService = servInfo;

            return newSeries;
        }

		public IList<SeriesMetadata> GetSeriesListInBox ( double xMin, double xMax, double yMin, double yMax )
		{
            throw new NotImplementedException();
            //string sql1 = DetailedSeriesSQLQuery();
            //string sqlWhere = " WHERE (Latitude BETWEEN " + yMin + " AND " + yMax + " AND Longitude BETWEEN " + xMin + " AND " + xMax + ")";
            //string sql = sql1 + sqlWhere;

            //DataTable seriesTable = _db.LoadTable("seriesTable", sql);

            //IList<SeriesMetadata> lst = new List<SeriesMetadata>();
            //foreach (DataRow row in seriesTable.Rows)
            //{
            //    SeriesMetadata newSeries = SeriesFromDataRow(row);
            //    lst.Add(newSeries);
            //}

            //return lst;
		}

		public IList<SeriesMetadata> GetSeriesListInBox ( double xMin, double xMax, double yMin, double yMax, string[] conceptCodes, DateTime startDate, DateTime endDate, int[] networkIDs )
		{
            throw new NotImplementedException();
            //string sql1 = DetailedSeriesSQLQuery();
            //string sqlWhere1 = string.Format(" WHERE Latitude > {0} AND Latitude < {1} AND Longitude > {2} AND Longitude < {3}",
            //    xMin, xMax, yMin, yMax);
            //string sqlWhere2 = "";

            ////concept keywords | variable names
            //if (conceptCodes.Length == 1)
            //{
            //    sqlWhere2 = " AND VariableName = '" + conceptCodes[0] + "'";
            //}
            //else if (conceptCodes.Length > 1)
            //{
            //    sqlWhere2 = " AND VariableName IN (";
            //    foreach (string keyword in conceptCodes)
            //    {
            //        sqlWhere2 += "'" + keyword + "',";
            //    }
            //    if (sqlWhere2.EndsWith(","))
            //    {
            //        sqlWhere2 = sqlWhere2.Substring(0, sqlWhere2.Length - 1);
            //    }
            //    sqlWhere2 += ")";
            //}

            ////date and time
            //string sqlWhere3 = " AND ( (BeginDateTime < @p1 AND EndDateTime > @p2) OR (BeginDateTime > @p1 AND BeginDateTime <= @p2) OR (EndDateTime > @p1 AND EndDateTime <= @p2) )";

            ////network IDs
            //string sqlWhere4 = "";

            //if (networkIDs != null)
            //{
            //    if (networkIDs.Length > 0)
            //    {
            //        sqlWhere4 = " AND DataSeriesCache.ServiceID IN (";
            //        foreach (int servID in networkIDs)
            //        {
            //            sqlWhere4 += servID.ToString() + ",";
            //        }
            //        if (sqlWhere4.EndsWith(","))
            //        {
            //            sqlWhere4 = sqlWhere4.Substring(0, sqlWhere4.Length - 1);
            //        }
            //        sqlWhere4 += ")";
            //    }
            //}

            //string sql = sql1 + sqlWhere1 + sqlWhere2 + sqlWhere3 + sqlWhere4;
            //DbCommand cmd = _db.CreateCommand(sql);
            //_db.AddParameter(cmd, "@p1", DbType.DateTime);
            //_db.AddParameter(cmd, "@p2", DbType.DateTime);
            //cmd.Parameters[0].Value = startDate;
            //cmd.Parameters[1].Value = endDate;
            
            //DataTable seriesTable = _db.LoadTable("seriesTable", cmd);

            //IList<SeriesMetadata> lst = new List<SeriesMetadata>();
            //foreach (DataRow row in seriesTable.Rows)
            //{
            //    SeriesMetadata newSeries = SeriesFromDataRow(row);
            //    lst.Add(newSeries);
            //}

            //return lst;
		}

		/// <summary>
		/// Gets all variables that are currently stored in the metadata cache database
		/// </summary>
		public IList<Variable> GetVariables ()
		{
            ISession session = _hibernate.GetCurrentSession();
            ICriteria criteria = session.CreateCriteria<Variable>();
            return criteria.List<Variable>();
            
            //string sqlVariables = "SELECT * FROM Variables";
            //return null;
		}

		/// <summary>
		/// Saves a new data service object to the database. If an entry with the same
		/// web service URL already exists in the database, update it.
		/// </summary>
		/// <param name="service"></param>
		public void SaveDataService ( DataServiceInfo service )
		{

            _hibernate.Save(service);
		}

		/// <summary>
		/// Deletes all entries in the metadata cache database that were
		/// added by the data service
		/// </summary>
		/// <param name="deleteService">Set to true if the record in the DataServices
		/// table should also be deleted. Set to false if the record in the DataServices
		/// table should be kept</param>
		/// <returns>The total number of records deleted</returns>
		public int DeleteRecordsForService ( DataServiceInfo service, bool deleteService )
		{
		    //throw new NotImplementedException();
            //string serviceID = service.Id.ToString();

            //string sqlDelete = "DELETE FROM DataSeriesCache WHERE ServiceID = " + serviceID + "; " +
            //    "DELETE FROM SitesCache WHERE ServiceID = " + serviceID + "; " +
            //    "DELETE FROM VariablesCache WHERE ServiceID = " + serviceID + "; " +
            //    "DELETE FROM SourcesCache WHERE ServiceID = " + serviceID + "; " +
            //    "DELETE FROM MethodsCache WHERE ServiceID = " + serviceID + "; " +
            //    "DELETE FROM QualityControlLevelsCache WHERE ServiceID = " + serviceID + ";";


            //using (DbConnection conn = _db.CreateConnection())
            //{
            //    conn.Open();

            //    using (DbTransaction tran = conn.BeginTransaction())
            //    {
            //        using (DbCommand cmd01 = conn.CreateCommand())
            //        {
            //            cmd01.CommandText = sqlDelete;
            //            cmd01.ExecuteNonQuery();
            //        }
            //        tran.Commit();
            //    }

            //}
            return 0;
            
            //DataTable seriesTable = _db.LoadTable(sql);
            //int numDeleted = 0;
            //foreach (DataRow row in seriesTable.Rows)
            //{
            //    int seriesID = Convert.ToInt32(row[0]);
            //    DeleteSeries(seriesID);
            //    numDeleted++;
            //}
            //return numDeleted;
		}


        /// <summary>
        /// Deletes a series given it's ID. The series is only deleted when it belongs to one theme.
        /// </summary>
        /// <param name="seriesID">The database ID of the series</param>
        /// <returns>true if series was deleted, false otherwise</returns>
        public bool DeleteSeries(int seriesID)
        {
            throw new NotImplementedException();
            //int siteID = 0;
            //int variableID = 0;
            //int sourceID = 0;
            //int qualityID = 0;
            //int methodID = 0;


            //string sqlSeries = "SELECT SiteID, VariableID, MethodID, SourceID, QualityControlLevelID " +
            //    "FROM DataSeriesCache WHERE SeriesID = " + seriesID;

            //DataTable seriesTable = _db.LoadTable("seriesTable", sqlSeries);
            
            //if (seriesTable.Rows.Count == 0) return false;

            //DataRow seriesRow = seriesTable.Rows[0];
            //siteID = Convert.ToInt32(seriesRow["SiteID"]);
            //variableID = Convert.ToInt32(seriesRow["VariableID"]);
            //methodID = Convert.ToInt32(seriesRow["MethodID"]);
            //sourceID = Convert.ToInt32(seriesRow["SourceID"]);
            //qualityID = Convert.ToInt32(seriesRow["QualityControlLevelID"]);

            ////SQL Queries
            //string sqlSite = "SELECT SiteID from DataSeriesCache where SiteID = " + siteID;
            //string sqlVariable = "SELECT VariableID from DataSeriesCache where VariableID = " + variableID;
            //string sqlSource = "SELECT SourceID from DataSeriesCache where SourceID = " + sourceID;
            //string sqlMethod = "SELECT MethodID from DataSeriesCache where MethodID = " + methodID;
            //string sqlQuality = "SELECT QualityControlLevelID from DataSeriesCache where QualityControlLevelID = " + qualityID;


            ////SQL Delete Commands
            //string sqlDeleteSeries = "DELETE FROM DataSeriesCache WHERE SeriesID = " + seriesID;

            //string sqlDeleteSite = "DELETE FROM SitesCache WHERE SiteID = " + siteID;
            //string sqlDeleteVariable = "DELETE FROM VariablesCache WHERE VariableID = " + variableID;
            //string sqlDeleteMethod = "DELETE FROM MethodsCache WHERE MethodID = " + methodID;
            //string sqlDeleteSource = "DELETE FROM SourcesCache WHERE SourceID = " + sourceID;
            //string sqlDeleteQuality = "DELETE FROM QualityControlLevelsCache WHERE QualityControlLevelID = " + qualityID;

            //DataTable tblSite = new DataTable();
            //DataTable tblVariable = new DataTable();
            //DataTable tblSource = new DataTable();
            //DataTable tblMethod = new DataTable();
            //DataTable tblQuality = new DataTable();

            ////Begin Transaction
            //using (DbConnection conn = _db.CreateConnection())
            //{
            //    conn.Open();

            //    using (DbTransaction tran = conn.BeginTransaction())
            //    {
            //        // get site IDs
            //        using (DbCommand cmd01 = conn.CreateCommand())
            //        {
            //            cmd01.CommandText = sqlSite;
            //            tblSite = _db.LoadTable("t1", cmd01);
            //        }

            //        // get variable IDs
            //        using (DbCommand cmd02 = conn.CreateCommand())
            //        {
            //            cmd02.CommandText = sqlVariable;
            //            tblVariable = _db.LoadTable("t2", cmd02);
            //        }

            //        // get source IDs
            //        using (DbCommand cmd03 = conn.CreateCommand())
            //        {
            //            cmd03.CommandText = sqlSource;
            //            tblSource = _db.LoadTable("t3", cmd03);
            //        }

            //        // get method IDs
            //        using (DbCommand cmd04 = conn.CreateCommand())
            //        {
            //            cmd04.CommandText = sqlMethod;
            //            tblMethod = _db.LoadTable("t4", cmd04);
            //        }

            //        // get qualityControl IDs
            //        using (DbCommand cmd05 = conn.CreateCommand())
            //        {
            //            cmd05.CommandText = sqlQuality;
            //            tblQuality = _db.LoadTable("t5", cmd05);
            //        }

            //        //delete the site
            //        if (tblSite.Rows.Count == 1)
            //        {
            //            using (DbCommand cmdDeleteSite = conn.CreateCommand())
            //            {
            //                cmdDeleteSite.CommandText = sqlDeleteSite;
            //                cmdDeleteSite.ExecuteNonQuery();
            //            }
            //        }

            //        //delete the variable
            //        if (tblVariable.Rows.Count == 1)
            //        {
            //            using (DbCommand cmdDeleteVariable = conn.CreateCommand())
            //            {
            //                cmdDeleteVariable.CommandText = sqlDeleteVariable;
            //                cmdDeleteVariable.ExecuteNonQuery();
            //            }
            //        }

            //        //delete the method
            //        if (tblMethod.Rows.Count == 1)
            //        {
            //            using (DbCommand cmdDeleteMethod = conn.CreateCommand())
            //            {
            //                cmdDeleteMethod.CommandText = sqlDeleteMethod;
            //                cmdDeleteMethod.ExecuteNonQuery();
            //            }
            //        }

            //        //delete the source
            //        if (tblSource.Rows.Count == 1)
            //        {
            //            using (DbCommand cmdDeleteSource = conn.CreateCommand())
            //            {
            //                cmdDeleteSource.CommandText = sqlDeleteSource;
            //                cmdDeleteSource.ExecuteNonQuery();
            //            }
            //        }

            //        //delete the quality control level
            //        if (tblQuality.Rows.Count == 1)
            //        {
            //            using (DbCommand cmdDeleteQuality = conn.CreateCommand())
            //            {
            //                cmdDeleteQuality.CommandText = sqlDeleteQuality;
            //                cmdDeleteQuality.ExecuteNonQuery();
            //            }
            //        }  

            //        //finally delete the series
            //        using (DbCommand cmdDeleteSeries = conn.CreateCommand())
            //        {
            //            cmdDeleteSeries.CommandText = sqlDeleteSeries;
            //            cmdDeleteSeries.ExecuteNonQuery();
            //        }

            //        //commit transaction
            //        tran.Commit();
            //    }
            //}
            //return true;
        }



		/// <summary>
		/// Check if the series with the same site, variable, method,
		/// source, quality control level and data service already
		/// exists in the database.
		/// </summary>
		/// <param name="seriesToCheck">the series to be checked</param>
		/// <returns>The series from the db, or NULL if it doesn't exist</returns>
		private SeriesMetadata CheckIfSeriesExists ( SeriesMetadata seriesToCheck )
		{
            throw new NotImplementedException();
		}

		/// <summary>
		/// Saves the series metadata to the metadata cache database.
		/// This method also automatically saves the site, variable,
		/// method, source and quality control level of the series.
		/// </summary>
		/// <param name="series">The series to be saved</param>
		/// <param name="dataService">The web service containing the series</param>
		public void SaveSeries ( SeriesMetadata series, DataServiceInfo dataService )
		{
            //string sqlSite = "SELECT SiteID FROM SitesCache WHERE SiteCode = ?";
            //string sqlVariable = "SELECT VariableID FROM VariablesCache WHERE VariableCode = ?";            
            //string sqlMethod = "SELECT MethodID FROM MethodsCache WHERE MethodDescription = ?";
            //string sqlSource = "SELECT SourceID FROM SourcesCache WHERE Organization = ?";          
            //string sqlQuality = "SELECT QualityControlLevelID FROM QualityControlLevelsCache WHERE Definition = ?";           
            //string sqlRowID = "; SELECT LAST_INSERT_ROWID();";
            
            //string sqlSaveSite = "INSERT INTO SitesCache(SiteCode, SiteName, Latitude, Longitude, LatLongDatumSRSID, LatLongDatumName, " +
            //    "Elevation_m, VerticalDatum, LocalX, LocalY, LocalProjectionSRSID, LocalProjectionName, " +
            //    "PosAccuracy_m, State, County, Comments, ServiceID) " +
            //    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

            //string sqlSaveVariable = "INSERT INTO variablesCache(VariableCode, VariableName, Speciation, " +
            //    "SampleMedium, ValueType, DataType, GeneralCategory, NoDataValue, VariableUnitsName, VariableUnitsType, VariableUnitsAbbreviation, " +
            //    "IsRegular, TimeSupport, TimeUnitsName, TimeUnitsType, TimeUnitsAbbreviation, ServiceID) " +
            //    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

            //string sqlSaveMethod = "INSERT INTO MethodsCache(OriginMethodID, MethodDescription, MethodLink, ServiceID) VALUES(?, ?, ?, ?)" + sqlRowID;

            //string sqlSaveQualityControl = "INSERT INTO QualityControlLevelsCache(OriginQualityControlLevelID, QualityControlLevelCode, Definition, Explanation) " +
            //    "VALUES(?,?,?,?)" + sqlRowID;

            //string sqlSaveSource = "INSERT INTO SourcesCache(OriginSourceID, Organization, SourceDescription, SourceLink, ContactName, Phone, " +
            //                       "Email, Address, City, State, ZipCode, Citation, MetadataID) " +
            //                       "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

            //string sqlSaveISOMetadata = "INSERT INTO ISOMetadataCache(TopicCategory, Title, Abstract, ProfileVersion, MetadataLink) " +
            //                        "VALUES(?, ?, ?, ?, ?)" + sqlRowID;

            //string sqlSaveSeries = "INSERT INTO DataSeriesCache(SiteID, VariableID, MethodID, SourceID, QualityControlLevelID, " +
            //    "BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ValueCount, ServiceID) " +
            //    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + sqlRowID;

            //int siteID = 0;
            //int variableID = 0;
            
            //int methodID = 0;
            //int qualityControlLevelID = 0;
            //int sourceID = 0;
            //int isoMetadataID = 0;
            //int seriesID = 0;
            

            //object siteIDResult = null;
           
            //object variableIDResult = null;
            
            //object methodIDResult = null;
            //object qualityControlLevelIDResult = null;
            //object sourceIDResult = null;
            
            //object seriesIDResult = null;
            
            ////check the ServiceID (must be already set)
            //if (dataService.Id <= 0)
            //{
            //    throw new ArgumentException("The DataServiceID must be set.");
            //}


            ////Step 1 Begin Transaction
            //using (DbConnection conn = _db.CreateConnection())
            //{
            //    conn.Open();

            //    using (DbTransaction tran = conn.BeginTransaction())
            //    {
            //        //****************************************************************
            //        //*** Step 2 Site
            //        //****************************************************************
            //        using (DbCommand cmd01 = conn.CreateCommand())
            //        {
            //            cmd01.CommandText = sqlSite;
            //            cmd01.Parameters.Add(_db.CreateParameter(DbType.String, series.Site.Code));
            //            siteIDResult = cmd01.ExecuteScalar();
            //            if (siteIDResult != null)
            //            {
            //                siteID = Convert.ToInt32(siteIDResult);
            //            }
            //        }

            //        if (siteID == 0) //New Site needs to be created
            //        {
            //            //Insert the site to the database
            //            using (DbCommand cmd04 = conn.CreateCommand())
            //            {
            //                Site site = series.Site;

            //                cmd04.CommandText = sqlSaveSite;
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Code));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Name));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Latitude));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Longitude));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, site.SpatialReference.SRSID));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.SpatialReference.SRSName));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.Elevation_m));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.VerticalDatum));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.LocalX));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.LocalY));
            //                if (site.LocalProjection != null)
            //                {
            //                    cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, site.LocalProjection.SRSID));
            //                    cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.LocalProjection.SRSName));
            //                }
            //                else
            //                {
            //                    cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, 0));
            //                    cmd04.Parameters.Add(_db.CreateParameter(DbType.String, "unknown"));
            //                }
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Double, site.PosAccuracy_m));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.State));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.County));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.String, site.Comments));
            //                cmd04.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));

            //                siteIDResult = cmd04.ExecuteScalar();
            //                siteID = Convert.ToInt32(siteIDResult);
            //            }
            //        }

            //        //****************************************************************
            //        //*** Step 3 Variable
            //        //****************************************************************
            //        Variable variable = series.Variable;

            //        using (DbCommand cmd05 = conn.CreateCommand())
            //        {
            //            cmd05.CommandText = sqlVariable;
            //            cmd05.Parameters.Add(_db.CreateParameter(DbType.String, variable.Code));
            //            cmd05.Parameters[0].Value = variable.Code;
            //            variableIDResult = cmd05.ExecuteScalar();
            //            if (variableIDResult != null)
            //            {
            //                variableID = Convert.ToInt32(variableIDResult);
            //            }
            //        }

            //        if (variableID == 0) //New variable needs to be created
            //        {
            //            //Insert the variable to the database
            //            using (DbCommand cmd09 = conn.CreateCommand())
            //            {
            //                cmd09.CommandText = sqlSaveVariable;
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Code));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Name));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.Speciation));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.SampleMedium));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.ValueType));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.DataType));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.GeneralCategory));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.Double, variable.NoDataValue));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.Name));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.UnitsType));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.VariableUnit.Abbreviation));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.Boolean, variable.IsRegular));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.Double, variable.TimeSupport));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.Name));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.UnitsType));
            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.String, variable.TimeUnit.Abbreviation));

            //                cmd09.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));

            //                variableIDResult = cmd09.ExecuteScalar();
            //                variableID = Convert.ToInt32(variableIDResult);
            //            }
            //        }

            //        //****************************************************************
            //        //*** Step 4 Method
            //        //****************************************************************
            //        Method method = series.Method;

            //        using (DbCommand cmd10 = conn.CreateCommand())
            //        {
            //            cmd10.CommandText = sqlMethod;
            //            cmd10.Parameters.Add(_db.CreateParameter(DbType.String, method.Description));
            //            methodIDResult = cmd10.ExecuteScalar();
            //            if (methodIDResult != null)
            //            {
            //                methodID = Convert.ToInt32(methodIDResult);
            //            }
            //        }

            //        if (methodID == 0)
            //        {
            //            using (DbCommand cmd11 = conn.CreateCommand())
            //            {
            //                cmd11.CommandText = sqlSaveMethod;
            //                cmd11.Parameters.Add(_db.CreateParameter(DbType.Int32, method.Code));
            //                cmd11.Parameters.Add(_db.CreateParameter(DbType.String, method.Description));
            //                cmd11.Parameters.Add(_db.CreateParameter(DbType.String, method.Link));
            //                cmd11.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));
            //                methodIDResult = cmd11.ExecuteScalar();
            //                methodID = Convert.ToInt32(methodIDResult);
            //            }
            //        }

            //        //****************************************************************
            //        //*** Step 5 Quality Control Level
            //        //****************************************************************
            //        QualityControlLevel qc = series.QualityControlLevel;

            //        using (DbCommand cmd12 = conn.CreateCommand())
            //        {
            //            cmd12.CommandText = sqlQuality;
            //            cmd12.Parameters.Add(_db.CreateParameter(DbType.String, qc.Definition));
            //            qualityControlLevelIDResult = cmd12.ExecuteScalar();
            //            if (qualityControlLevelIDResult != null)
            //            {
            //                qualityControlLevelID = Convert.ToInt32(qualityControlLevelIDResult);
            //            }
            //        }

            //        if (qualityControlLevelID == 0)
            //        {
            //            //to set the code
            //            int qcCode = 0;
            //            int.TryParse(qc.Code, out qcCode);
                        
            //            using (DbCommand cmd13 = conn.CreateCommand())
            //            {
            //                cmd13.CommandText = sqlSaveQualityControl;
            //                cmd13.Parameters.Add(_db.CreateParameter(DbType.Int32, qcCode));
            //                cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Code));
            //                cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Definition));
            //                cmd13.Parameters.Add(_db.CreateParameter(DbType.String, qc.Explanation));
            //                cmd13.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));
            //                qualityControlLevelIDResult = cmd13.ExecuteScalar();
            //                qualityControlLevelID = Convert.ToInt32(qualityControlLevelIDResult);
            //            }
            //        }

            //        //****************************************************************
            //        //*** Step 6 Source
            //        //****************************************************************
            //        Source source = series.Source;

            //        using (DbCommand cmd14 = conn.CreateCommand())
            //        {
            //            cmd14.CommandText = sqlSource;
            //            cmd14.Parameters.Add(_db.CreateParameter(DbType.String, source.Organization));
            //            sourceIDResult = cmd14.ExecuteScalar();
            //            if (sourceIDResult != null)
            //            {
            //                sourceID = Convert.ToInt32(sourceIDResult);
            //            }
            //        }

            //        if (sourceID == 0)
            //        {
            //            using (DbCommand cmd17 = conn.CreateCommand())
            //            {
            //                cmd17.CommandText = sqlSaveSource;
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.Int32, source.OriginId));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Organization));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Description));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Link));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.ContactName));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Phone));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Email));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Address));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.City));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.State));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.Int32, source.ZipCode));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, source.Citation));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.String, isoMetadataID));
            //                cmd17.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));
            //                sourceIDResult = cmd17.ExecuteScalar();
            //                sourceID = Convert.ToInt32(sourceIDResult);
            //            }
            //        }

            //        //****************************************************************
            //        //*** Step 7 Series
            //        //****************************************************************
            //        using (DbCommand cmd18 = conn.CreateCommand())
            //        {
            //            cmd18.CommandText = sqlSaveSeries;
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, siteID));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, variableID));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, methodID));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, sourceID));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, qualityControlLevelID));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.DateTime, series.BeginDateTime));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.DateTime, series.EndDateTime));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.DateTime, series.BeginDateTimeUTC));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.DateTime, series.EndDateTimeUTC));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, series.ValueCount));
            //            cmd18.Parameters.Add(_db.CreateParameter(DbType.Int32, dataService.Id));
                        
            //            seriesIDResult = cmd18.ExecuteScalar();
            //            seriesID = Convert.ToInt32(seriesIDResult);
            //        }

            //        //Commit Transaction
            //        tran.Commit();
            //    }
            //    conn.Close();
            //}
		}

        /// <summary>
        /// updates the data row corresponding to the serviceInfo object
        /// The following parameters are updated:
        /// IsHarvested
        /// HarveDateTime
        /// ServiceName
        /// ServiceVersion
        /// ServiceType
        /// ServiceProtocol
        /// EastLongitude
        /// WestLongitude
        /// EastLatitude
        /// WestLatitude
        /// </summary>
        /// <param name="serviceInfo"></param>
        public void UpdateDataRow(DataServiceInfo serviceInfo)
        {
            //string sql = "UPDATE DataServices SET " +
            //    "IsHarvested=?,HarveDateTime=?,ServiceName=?,ServiceVersion=?,ServiceType=?,ServiceProtocol=?," +
            //    "EastLongitude=?,WestLongitude=?,NorthLatitude=?,SouthLatitude=? WHERE ServiceID = ?";

            //using (DbConnection conn = _db.CreateConnection())
            //{
            //    conn.Open();

            //    using (DbTransaction tran = conn.BeginTransaction())
            //    {
            //        using (DbCommand cmd = conn.CreateCommand())
            //        {
            //            cmd.CommandText = sql;
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Boolean, serviceInfo.IsHarvested));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.DateTime, serviceInfo.HarveDateTime));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.String, serviceInfo.ServiceName));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.String, serviceInfo.Version));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.String, serviceInfo.ServiceType));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.String, serviceInfo.Protocol));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Double, serviceInfo.EastLongitude));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Double, serviceInfo.WestLongitude));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Double, serviceInfo.NorthLatitude));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Double, serviceInfo.SouthLatitude));
            //            cmd.Parameters.Add(_db.CreateParameter(DbType.Int32, serviceInfo.Id));

            //            cmd.ExecuteNonQuery();
            //        }
                                        
            //        tran.Commit();
            //    }
            //}
        }
	}
}
